/**
 * ______________________________________________________________________________
 *
 * File: UpdateUsersFromLOS.java
 *______________________________________________________________________________
 *
 * CreatedBy: Verinon
 * CreationDate: Nov 6, 2012   10:36:43 AM   2012
 * Description: Connect to middle database using dblink, get all users from these and update in ecm(if not exist create vb_user object else 
 *              update vb_user object), add those users to respective groups
 * what functions are doing
 *______________________________________________________________________________
 *
 * Copyright: (c) Vietinbank, all rights reserved 
 *______________________________________________________________________________
 *
 *	Create DB LINK
 *
 *	--SQL Script
		#drop and create VBLOSUSERTABLE # Login into SQL PLUS using Repo Owner: ECM_REPO_XXX (XXX=UAT or PROD); This DB user should have Create and Drop DBLINK, SYNONYM
		DROP SYNONYM VBLOSUSERTABLE
		DROP SYNONYM VBLOSDEPTTABLE
		DROP  PUBLIC DATABASE LINK VBLOSDBLINK
		#In Actual UAT or PROD user ECM_LOS instead SYSTEM Account: ORCL = LOSDB
		#CREATE PUBLIC DATABASE LINK VBLOSDBLINK CONNECT TO SYSTEM IDENTIFIED BY oracle USING '192.168.100.142:1521/ORCL'
		CREATE PUBLIC DATABASE LINK VBLOSDBLINK CONNECT TO ECM_LOS IDENTIFIED BY ECM_LOS USING '10.6.129.152:1521/LOSDB'
		CREATE OR REPLACE SYNONYM VBLOSUSERTABLE FOR US001@VBLOSDBLINK
		CREATE OR REPLACE SYNONYM VBLOSDEPTTABLE FOR PF124@VBLOSDBLINK

	--DQL Script
		REGISTER TABLE dm_dbo.VBLOSDOCLIST(unknown int)
		go
		UPDATE dm_registered objects set owner_table_permit=15 set group_table_permit = 3 set world_table_permit = 3 where object_name='VBLOSDOCLIST'
		go
		
		REGISTER TABLE dm_dbo.VBLOSUSERTABLE(unknown char(16))
		go
		UPDATE dm_registered OBJECTS SET owner_table_permit = 15 SET group_table_permit = 3 SET world_table_permit = 3 WHERE object_name = 'VBLOSUSERTABLE'
		go
		
		REGISTER TABLE dm_dbo.VBLOSDEPTTABLE(unknown char(16))
		go
		UPDATE dm_registered OBJECTS SET owner_table_permit = 15 SET group_table_permit = 3 SET world_table_permit = 3 WHERE object_name = 'VBLOSDEPTTABLE'
		go
		SELECT * FROM dm_dbo.LOSUSERTABLE;
		
	--On Repository	
		Create java method from da 'updateuserfromlosjob' method verbe: com.vb.ecm.server.jobs.UpdateUsersFromLOS
        Create job from da UpdateUsersFromLOStoECM
        
    --Description: Connect to middle database using dblink, get all users from these and update in ecm(if not exist create vb_user object else 
                   update vb_user object), add those users to respective groups
   
    --Job Name: UpdateUsersFromLOStoECM
      Job Method name: updateuserfromlosjob
          					    					    					
    --job args: -username ecmdmadmin  ------- Superuser user login id
                -password Abc@123456  ------- Superuser user password
                
                to run cmd mode args are:
	                -docbase_name ECM_REPO_PROD
					-user_name dmadmin
					-ticket dmadmin
					-job_id 0801e24080041f3b
					-method_trace_level 0               
                
    --job logs: Content Server Machine: E:\Documentum\jboss5.1.0\server\DctmServer_MethodServer\logs\customjobs.log
    		    E:\Documentum\jboss5.1.0\server\DctmServer_MethodServer\log\server.log  
     
        
 */
package com.vb.ecm.server.jobs;

import java.io.PrintWriter;
import java.util.Iterator;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

import com.documentum.fc.client.DfClient;
import com.documentum.fc.client.DfQuery;
import com.documentum.fc.client.IDfClient;
import com.documentum.fc.client.IDfCollection;
import com.documentum.fc.client.IDfGroup;
import com.documentum.fc.client.IDfQuery;
import com.documentum.fc.client.IDfSession;
import com.documentum.fc.client.IDfSessionManager;
import com.documentum.fc.client.IDfUser;
import com.documentum.fc.common.DfException;
import com.documentum.fc.common.DfId;
import com.documentum.fc.common.DfLogger;
import com.documentum.fc.common.DfLoginInfo;
import com.documentum.fc.common.IDfId;
import com.documentum.fc.common.IDfLoginInfo;
import com.documentum.fc.methodserver.DfMethodArgumentManager;
import com.documentum.fc.methodserver.IDfMethod;
import com.documentum.server.impl.method.common.SessionManagerManager;
import com.documentum.server.impl.method.report.IReportWriter;
import com.documentum.server.impl.method.report.ReportFactory;

public class UpdateUsersFromLOS implements IDfMethod {

	private static final Logger LOGGER = DfLogger.getLogger(UpdateUsersFromLOS.class);

	private static IReportWriter reportWriter;
	private IDfSession session = null;
	private IDfCollection losuserColl = null;
	private IDfUser losUserObj = null;
	IDfQuery losUserQueryString = null;
	private String docbaseName = null, userName = null, loginTicket = null, id = null;
	private int methodTraceLevel = 0;
	private ResourceBundle rsb = null;
	private String losUserID = null;
	private IDfSessionManager sessionManager = null;

	@SuppressWarnings("unchecked")
	public int execute(Map args, PrintWriter writer) throws Exception {

		try {
			LOGGER.debug("Enter into execute() ");
			
			ReportFactory rf = new ReportFactory();
			IDfId jobId = new DfId(id);
			LOGGER.debug("ID:" + id);
			
			DfMethodArgumentManager argsObj = new DfMethodArgumentManager(args);
			LOGGER.debug("Before Creating Session");
			session = SessionManagerManager.getSession(docbaseName, userName, null, null);
	
			LOGGER.debug("Session Created Successfully." + session);
			rsb = ResourceBundle.getBundle("com.vb.ecm.server.jobs.updateusersconfig");
	
			losuserColl = getLOSUsersFromLOSDB();

			while (losuserColl.next()) {
				losUserID = losuserColl.getString(rsb.getString("USERID_R"));
				LOGGER.debug("losUserID:" + losUserID);
				losUserObj = (IDfUser) session.getObjectByQualification("vb_user where user_login_name='" + losUserID + "'");
				try {
					if (losUserObj == null) {
						// create vb_user object
						LOGGER.debug("Start Creating New User Object:"+losUserID);
						IDfUser losUserObj = (IDfUser) session.newObject(rsb.getString("USEROBJECTTYPE"));
						losUserObj.setUserLoginName(losUserID);
						losUserObj.setUserName(losuserColl.getString(rsb.getString("USERNAME_R")));
						// losUserObj.setUserAddress(
						// losuserColl.getString(rsb.getString("USEREMAIL_R")));
						losUserObj.setDefaultFolder("/" + losUserID, true);
						losUserObj.setString("user_login_domain", rsb.getString("USERLOGINDOMAIN"));
						losUserObj.setString("user_source", rsb.getString("USERSOURCE"));
						// losUserObj.setString(rsb.getString("USERROLE_U"),
						// losuserColl.getString(rsb.getString("USERROLE_R")));
						losUserObj.setString(rsb.getString("BRANCHNUMBER_U"), losuserColl.getString(rsb.getString("USERBRANCH_R")));
						losUserObj.setString(rsb.getString("DEPTNAME_U"), losuserColl.getString(rsb.getString("USERDEPTNAME_R")));
						losUserObj.save();
						LOGGER.debug("User Created with User Object:"+losUserID);

					} else {
			
						unassignUserFromGroup(losUserID);
						// update vb_user object
						LOGGER.debug("Updating Existing User Object:"+losUserID);
					
						losUserObj.setString(rsb.getString("BRANCHNUMBER_U"), losuserColl.getString(rsb.getString("USERBRANCH_R")));
						losUserObj.setString(rsb.getString("DEPTNAME_U"), losuserColl.getString(rsb.getString("USERDEPTNAME_R")));
						losUserObj.save();
						LOGGER.debug("Updated Existing User Object:"+losUserID);

					}

				} catch (Exception e) {
					// TODO Auto-generated catch block
					LOGGER.error("User already exist continue to next:"+losUserID);
					e.printStackTrace();
				}
			
			}

			// respective groups
			getUsers(session);

			// closing print writer
	

		} catch (Exception e) {
			LOGGER.error("Error in Execute Method : ", e.fillInStackTrace());

		} finally {
				SessionManagerManager.release(session);
		}
		LOGGER.debug("Exit from execute() ");
		return 0;
	}

	/**
	 * @param losUserID2
	 *            This method get all groups that user @losUserID2 belongs to
	 *            Remove this user from group, repeat for all belong groups
	 */
	private void unassignUserFromGroup(String losUserID2) throws DfException {
		// TODO Auto-generated method stub
		// select group_name from dm_group where any users_names ='LOSUser3';
		// alter group 'vb_100_retail_group' drop 'LOSUser3';
		LOGGER.debug("Enter into unassignUserFromGroup() ");
		IDfCollection groupObjColl = null, removeUserColl = null;
		IDfQuery groupQuery, removeUserQuery = null;
		String groupName, strGroupQuery, strRemoveUserQuery = null;
		try {

			strGroupQuery = "select group_name from dm_group where any users_names ='" + losUserID2 + "' and group_class='group'";
			groupQuery = new DfQuery();
			removeUserQuery = new DfQuery();
			groupQuery.setDQL(strGroupQuery);
			groupObjColl = groupQuery.execute(session, IDfQuery.DF_EXEC_QUERY);
			while (groupObjColl.next()) {
				groupName = groupObjColl.getString("group_name");
				//
				strRemoveUserQuery = "alter group '" + groupName + "' drop '" + losUserID2 + "'";

				removeUserQuery.setDQL(strRemoveUserQuery);
				removeUserColl = removeUserQuery.execute(session, IDfQuery.DF_EXEC_QUERY);
				while (removeUserColl.next()) {
					// alter done
				}
			}
		} catch (Exception e) {
			LOGGER.debug("Error in unassignUserFromGroup() : " + e);
			e.printStackTrace();
		} finally {
			groupObjColl.close();
			LOGGER.debug("collection closed");
		}
		LOGGER.debug("Exit from unassignUserFromGroup() ");

	}

	/**
	 * Method Name : getBranchDeptUsers Description : This method will get all
	 * the VB users from ecm and send each user to addUsersToGroup() method
	 * 
	 * @param session
	 * @throws DfException
	 **/
	
	private void getUsers(IDfSession session) throws DfException {
		LOGGER.debug("Enter into getUsers() ");
		IDfCollection objIDfCollection = null;
		String branchCode = null, userName = null, strQuery = null, departement = null;
		IDfQuery query = null;
		try {
		
			strQuery = "select user_name,branch_number,dept_name from vb_user " + "where branch_number !=' ' and dept_name !=' ' order by dept_name";
			query = new DfQuery();
			query.setDQL(strQuery);
			objIDfCollection = query.execute(session, IDfQuery.DF_EXEC_QUERY);
			while (objIDfCollection.next()) {
				userName = objIDfCollection.getString("user_name");
				branchCode = objIDfCollection.getString("branch_number");
				departement = objIDfCollection.getString("dept_name");
				
				// Assign users to respective groups
				addUsersToGroup(userName, branchCode, departement, session);
			} 
		} catch (Exception e) {
			LOGGER.debug("Error in getUsers method : " + e);
			e.printStackTrace();
		} finally {
			objIDfCollection.close();
			LOGGER.debug("collection closed");
		}
		LOGGER.debug("Exit from getUsers() ");
	}

	/**
	 * Method Name : addUsersToGroup Description : This method will add user to
	 * groups based on user dept_code and branch number combination Dept Code
	 * and Customer type relation maintained in updateuserconfig.properties file
	 * 
	 * @param username
	 *            ,branch,department,session
	 * @throws DfException
	 **/
	private void addUsersToGroup(String username, String branch, String department, IDfSession session) throws DfException {
		LOGGER.debug("Enter into addUsersToGroup() ");
		String groupName = null, strQualification = null;
		IDfId groupId = null;
		IDfGroup objIDfGroup = null;		
		try {

			if ((rsb.getString(department)).indexOf("C") != -1) {
				groupName = "vb_" + branch + "_enterprise_group"; // vb_100_retail_group
				strQualification = "dm_group WHERE group_class ='group' and group_name = '" + groupName + "'";
				groupId = session.getIdByQualification(strQualification);
				objIDfGroup = (IDfGroup) session.getObject(groupId);
				if (objIDfGroup.isUserInGroup(username)) {
				
				} else {
					objIDfGroup.addUser(username);
					objIDfGroup.save();

				}
			}
			if ((rsb.getString(department)).indexOf("I") != -1) {
				groupName = "vb_" + branch + "_retail_group";
				strQualification = "dm_group WHERE group_class ='group' and group_name = '" + groupName + "'";
				LOGGER.debug("strQualification:" + strQualification);
				groupId = session.getIdByQualification(strQualification);
				objIDfGroup = (IDfGroup) session.getObject(groupId);
				if (objIDfGroup.isUserInGroup(username)) {
			
				} else {
					objIDfGroup.addUser(username);
					objIDfGroup.save();
			
				}
			}
			if ((rsb.getString(department)).indexOf("F") != -1) {
				groupName = "vb_" + branch + "_fi_group";
				strQualification = "dm_group WHERE group_class ='group' and group_name = '" + groupName + "'";
				groupId = session.getIdByQualification(strQualification);
				objIDfGroup = (IDfGroup) session.getObject(groupId);
				if (objIDfGroup.isUserInGroup(username)) {
	
				} else {
					objIDfGroup.addUser(username);
					objIDfGroup.save();
			
				}
			}
			
		} catch (Exception e) {
			LOGGER.debug("Error in addUsersToGroup Method" + e);
			e.printStackTrace();
		}
		LOGGER.debug("Exit from addUsersToGroup() ");
	}

	/**
	 * Method Name : getLOSUsersFromLOSDB Description : This method will execute
	 * query on registered table and return collection object.
	 * 
	 * @param
	 * @throws DfException
	 **/

	IDfCollection getLOSUsersFromLOSDB() throws DfException {
		LOGGER.debug("Enter into getLOSUsersFromLOSDB() ");
		String losUserQuery = rsb.getString("LOSUSERQUERY");
		losUserQueryString = new DfQuery();
		losUserQueryString.setDQL(losUserQuery);
		LOGGER.debug("losUserQuery:" + losUserQuery);
		losuserColl = losUserQueryString.execute(session, IDfQuery.DF_EXEC_QUERY);
		LOGGER.debug("losUserQuery Executed");
		LOGGER.debug("Exit from getLOSUsersFromLOSDB() ");
		return losuserColl;
	}

	/**
	 * Method which creats session manager object called from execute method in
	 * the current class
	 * 
	 */

	private IDfSessionManager login(String username, String password, String docbase) throws DfException {

		LOGGER.debug("Enter into login() ");
		IDfClient dfClient = DfClient.getLocalClient();
		if (dfClient != null) {
			LOGGER.debug(username + ";" + password + ";" + docbase);
			IDfLoginInfo li = new DfLoginInfo();
			li.setUser(username);
			li.setPassword(password);
			li.setDomain(null);
			sessionManager = dfClient.newSessionManager();
			sessionManager.setIdentity(docbase, li);
			LOGGER.debug("Exit from login() ");
			return sessionManager;
		}
		return null;
	}

	/**
	 * @param args
	 *            to run cmd mode args are: -docbase_name ECM_REPO_PROD
	 *            -user_name dmadmin -ticket dmadmin -job_id 0801e24080041f3b
	 *            -method_trace_level 0
	 * @throws Exception
	 */
	public static void main(String args[]) throws Exception {
		//TODO: this class to test some functions in ECM ( not use web service )
		
		LOGGER.debug("Enter into main()");
		UpdateUsersFromLOS uqp = new UpdateUsersFromLOS();
		uqp.execute(DfMethodArgumentManager.getArgumentsFromCommandLine(args), new PrintWriter(System.out));
		LOGGER.debug("Exit from main()");
	}
}
